﻿Imports System.Text.RegularExpressions
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class frmGestionClients
    Dim dbClients As New cls_taco
    Dim idClient As Integer
    Dim saisie As Boolean
    Dim messageErreur As String

    Public Overloads Function ShowDialog(ByVal v_saisie As Boolean) As Windows.Forms.DialogResult

        If v_saisie = True Then
            saisie = True
            btnSelectionner.Visible = True
            statuClient.Items.Add("Sélectionner un client en cliquant sur un client dans la liste et en cliquant sur 'sélectionner'")
            btnSelectionner.Enabled = False
        Else
            saisie = False
            btnSelectionner.Visible = False
        End If


        Return MyBase.ShowDialog()
    End Function


    Private Sub frmGestionClients_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'MsgBox(validerNom("Martin Desharnais/", messageErreur, False))
        ' MsgBox(validerTel("$$$$$", messageErreur, False))

        btnSupprimer.Enabled = False
        btnModifier.Enabled = False
        btnRechercheDetaillee.PerformClick()
        btnRechercher.PerformClick()
        remplirListCbo("province", "Tous", cboProvince, "")
        remplirListCbo("ville", "Tous", cboVille, " WHERE v.CodeProvince = p.CodeProvince AND p.nomProvince = '" & cboProvince.SelectedItem & "'")

       

    End Sub

    Private Sub btnRechercheDetaillee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechercheDetaillee.Click
        If panRecherche.Visible = True Then
            panRecherche.Visible = False
            lstResultat.Height += panRecherche.Height
            lstResultat.Top -= panRecherche.Height
            btnRechercheDetaillee.Text = "/\"
        Else
            panRecherche.Visible = True
            lstResultat.Height -= panRecherche.Height
            lstResultat.Top += panRecherche.Height
            btnRechercheDetaillee.Text = "\/"
        End If

    End Sub

    Private Sub btnQuitter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub conRechercheDetaille_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles panRecherche.Paint

    End Sub


    Public Function validerDonnee()
        Return validerTel(txtTel.Text, messageErreur, True) = True And validerNom(txtNom.Text, messageErreur, True) _
        And validerNom(txtPrenom.Text, messageErreur, True) And validerAdresse(txtAdresse.Text, messageErreur, True)
    End Function

    Private Sub btnRechercher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechercher.Click
        'If validerChamp() = False Then
        'MsgBox("Veuillez spécifier au moins un critère de recherche")
        messageErreur = "Erreur: "
        txtAdresse.Text = Trim(txtAdresse.Text)
        txtNom.Text = Trim(txtNom.Text)
        txtPrenom.Text = Trim(txtPrenom.Text)
        txtTel.Text = Trim(txtTel.Text)
        If validerDonnee() Then

            ' MsgBox(creerRequete)
            remplirListView()
            btnModifier.Enabled = False
            btnSupprimer.Enabled = False

        Else
            MsgBox(messageErreur)


        End If

    End Sub

    Private Sub frmRecherche_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

 

    Public Function validerChamp() As Boolean
        If txtTel.Text = Nothing And cboVille.Text = Nothing And txtAdresse.Text = Nothing And txtPrenom.Text = Nothing _
        And txtNom.Text = Nothing Then
            Return False
        End If

        Return True
    End Function


    Private Sub btnRaz_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRaz.Click
        txtNom.Text = Nothing
        txtAdresse.Text = Nothing
        txtPrenom.Text = Nothing
        cboProvince.Text = cboProvince.Items(0)
        cboVille.Text = cboVille.Items(0)
        txtTel.Text = Nothing
        chkLocation.Checked = False
        btnRechercher.PerformClick()
        chkEntreprise.Checked = False
        chkActif.Checked = True
    End Sub

    Private Sub btnAjouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAjouter.Click

        ' formEnAttente = Me
        InscriptionClients.ShowDialog(Nothing, False)

    End Sub

    Sub remplirListView()
        btnSupprimer.Enabled = False
        btnModifier.Enabled = False
        'créé la connection à la base de données
        dbClients = New cls_taco



        dbClients.defDataset(creerRequete(), "tblClients")



        'créé le dataSet
        'remplit le dataSet avec la requête SQL du dataAdapter
        'vide la listView
        lstResultat.Clear()

        'créé une colonne dans la listView pour chaque élément de la requête SQL
        'For Each colonne As DataColumn In dbClients.ds.Tables("tblClients").Columns
        For ctr = 0 To dbClients.ds.Tables("tblClients").Columns.count - 1
            lstResultat.Columns.Add(dbClients.ds.Tables("tblClients").Columns(ctr).columnName, 130)

            If lstResultat.Columns(ctr).text = "noClient" Then lstResultat.Columns(ctr).Width = 0
        Next

        'créé un élément dans le listView pour chaque enregistrement retourné par la requête SQL
        For Each client As DataRow In dbClients.ds.Tables("tblClients").Rows

            Dim LI As New ListViewItem
            LI.Text = client.Item(0)

            For ctr As Integer = 1 To dbClients.ds.Tables("tblClients").Columns.Count - 1
                LI.SubItems.Add(nvl(client.Item(ctr), ""))
            Next

            lstResultat.Items.Add(LI)

        Next

    End Sub

    Public Function creerRequete() As OleDbCommand

        Dim requete As String = "SELECT nom AS Nom, prenom AS Prenom, telephone as Telephone, adresse As Adresse, actif as Actif,noClient FROM tblClients "
        Dim maCommande As New OleDbCommand

        If cboProvince.Text <> "Tous" And cboProvince.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " CodeProvince = (select CodeProvince from tblProvinces where nomProvince = @province)"
            maCommande.Parameters.Add("@province", OleDbType.BSTR, 50).Value = cboProvince.Text
        End If

        If cboVille.Text <> "Tous" And cboVille.Text <> "" Then
            ' requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ville='Trois-Rivières'"
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ville = @ville"
            maCommande.Parameters.Add("@ville", OleDbType.BSTR, 50).Value = cboVille.Text
        End If

        If txtTel.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " telephone LIKE @telephone"
            maCommande.Parameters.Add("@telephone", OleDbType.BSTR, 50).Value = "%" & txtTel.Text & "%"
        End If
        If txtNom.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " nom LIKE @nom"
            maCommande.Parameters.Add("@nom", OleDbType.BSTR, 50).Value = "%" & txtNom.Text & "%"
        End If
        If chkEntreprise.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " noEntreprise is not null"

        End If
        If txtPrenom.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " prenom LIKE @prenom"
            maCommande.Parameters.Add("@prenom", OleDbType.BSTR, 50).Value = "%" & txtPrenom.Text & "%"
        End If
        If txtAdresse.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " adresse LIKE @adresse"
            maCommande.Parameters.Add("@adresse", OleDbType.BSTR, 50).Value = "%" & txtAdresse.Text & "%"
        End If
        If chkLocation.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " noClient in (select noClient from tblLocations where actif=true)"

        End If
      
     

        If chkActif.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " actif=true"
            ' Else
            '    requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " actif=false"
        End If

        maCommande.CommandText = requete
        'maCommande.Parameters.Add(New OleDb.OleDbParameter("@province", cboProvince.Text))
        ' MsgBox(maCommande.Parameters("@province").Value)
        Return maCommande
        ' 
        '"SELECT type AS Type, marque AS Marque, modele As Modèle " & _
        '  "FROM tblVehicules")
    End Function

  

    Private Sub cboProvince_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboProvince.SelectedIndexChanged

    End Sub

    Private Sub cboProvince_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProvince.SelectedValueChanged


    End Sub

    Private Sub cboProvince_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProvince.SelectionChangeCommitted
        remplirListCbo("ville", "Tous", cboVille, " WHERE v.CodeProvince = p.CodeProvince AND p.nomProvince = '" & cboProvince.SelectedItem & "'")
    End Sub

    Private Sub cboVille_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboVille.SelectedIndexChanged

    End Sub

    Private Sub cboVille_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboVille.SelectionChangeCommitted

    End Sub

    Private Sub btnSupprimer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSupprimer.Click
        Dim etatFinal As Boolean

        etatFinal = IIf(btnSupprimer.Text = "Supprimer", False, True)

        For Each client As DataRow In dbClients.ds.Tables("tblClients").Rows
            'si son numéro d'immatriculation correspond à celui sélectionné
            If client.Item(0) = lstResultat.SelectedItems(0).Text Then

                'créer un DataSet pour l'élément à modifier et mettre à jour ce dataSet là
                'ce dataSet condiendra un select * from tblVehicule where noImmatriculation = lstResultats.SelectedItems(0).Text


                'on désactive l'enregistrement
                If Not ObjHasNoIn(CInt(lstResultat.SelectedItems(0).SubItems(5).Text), "noClient", "tblLocations", True) Then
                    client.Item("actif") = etatFinal
                Else
                    MsgBox("Impossible de supprimer ce client, il a une location en encours")
                End If

                Exit For

            End If

        Next

        dbClients.update("tblClients")

        remplirListView()
    End Sub

    

    Private Sub lstResultat_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstResultat.MouseClick
        If lstResultat.SelectedItems(0).SubItems(4).Text = "True" Then
            btnSupprimer.Text = "Supprimer"
        Else
            btnSupprimer.Text = "Activer"
        End If

        btnSupprimer.Enabled = True
        btnModifier.Enabled = True

        If saisie = True Then
            btnSelectionner.Enabled = True
        Else
            btnSelectionner.Enabled = False
        End If
    End Sub

  

  

    Private Sub btnModifier_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModifier.Click
        idClient = lstResultat.SelectedItems(0).SubItems(5).Text

        InscriptionClients.ShowDialog(idClient, False)
    End Sub


    Private Sub lstResultat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstResultat.SelectedIndexChanged

    End Sub

    Private Sub btnSelectionner_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectionner.Click
        idClient = lstResultat.SelectedItems(0).SubItems(5).Text
        If frmModifListeNoire.IsHandleCreated Then frmModifListeNoire.updateInfoClient(idClient)
        'probleme ici
        Me.Close()
    End Sub

    Private Sub btnAnnuler_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnnuler.Click
        Me.Close()
    End Sub
End Class